NULL Functions

Table of Contents

1. Handle NULL – Data Aggregation

   • Average scores with COALESCE

2. Handle NULL – Mathematical Operators

   • Full name and bonus score

3. Handle NULL – Sorting Data

   • Sort with NULLs last

4. NULLIF – Division by Zero

   • Safe division for price

5. IS NULL – IS NOT NULL

   • Customers with no score
   • Customers with a score

6. LEFT ANTI JOIN

   • Customers with no orders

7. NULL vs Empty String vs Blank Spaces

   • Compare NULL / ‘’ / ’ ’

1. Handle NULL – Data Aggregation

1.1 Average scores with COALESCE

Task 1 – Compare averages with and without replacing NULL Find the average scores of customers from Sales.Customers. Show:

• CustomerID
• Score
• Score2 = Score with NULL replaced by 0 using COALESCE
• AvgScores = average of Score (standard AVG, ignores NULLs)
• AvgScores2 = average of Score2 (NULLs treated as 0)

Use COALESCE and window AVG().

SELECT
    CustomerID,
    Score,
    COALESCE(Score, 0) AS Score2,
    AVG(Score) OVER () AS AvgScores,
    AVG(COALESCE(Score, 0)) OVER () AS AvgScores2
FROM Sales.Customers;

2. Handle NULL – Mathematical Operators

2.1 Full name and bonus score

Task 2 – Build full name and add score bonus safely Display customers from Sales.Customers with:

• CustomerID, FirstName, LastName
• FullName = FirstName + ' ' + LastName, but handle NULL LastName
• Score
• ScoreWithBonus = Score + 10, treating NULL as 0

Use COALESCE for both name and score.

SELECT
    CustomerID,
    FirstName,
    LastName,
    FirstName + ' ' + COALESCE(LastName, '') AS FullName,
    Score,
    COALESCE(Score, 0) + 10 AS ScoreWithBonus
FROM Sales.Customers;

3. Handle NULL – Sorting Data

3.1 Sort with NULLs last

Task 3 – Order by score with NULLs at the end From Sales.Customers, return:

• CustomerID
• Score

Sort from lowest to highest score, but ensure that rows where Score is NULL appear after all non-NULL scores.

SELECT
    CustomerID,
    Score
FROM Sales.Customers
ORDER BY 
    CASE WHEN Score IS NULL THEN 1 ELSE 0 END,
    Score;

4. NULLIF – Division by Zero

4.1 Safe division for price

Task 4 – Compute price while avoiding division by zero From Sales.Orders, calculate the price as Sales / Quantity. Show:

• OrderID
• Sales
• Quantity
• Price = Sales / Quantity, but avoid division by zero using NULLIF

Rows with Quantity = 0 should have Price as NULL instead of causing an error.

SELECT
    OrderID,
    Sales,
    Quantity,
    Sales / NULLIF(Quantity, 0) AS Price
FROM Sales.Orders;

5. IS NULL – IS NOT NULL

5.1 Customers with no score

Task 5 – Find customers where Score is NULL Select all columns from Sales.Customers for customers whose Score is NULL.

SELECT *
FROM Sales.Customers
WHERE Score IS NULL;

5.2 Customers with a score

Task 6 – Find customers where Score is NOT NULL Select all columns from Sales.Customers for customers whose Score is not NULL.

SELECT *
FROM Sales.Customers
WHERE Score IS NOT NULL;

6. LEFT ANTI JOIN

6.1 Customers with no orders

Task 7 – List customers who never placed any order Using Sales.Customers (c) and Sales.Orders (o), return all details for customers who have not placed any orders.

• Use a LEFT JOIN on CustomerID
• Filter for rows where there is no match in Sales.Orders (left anti join pattern)

SELECT
    c.*,
    o.OrderID
FROM Sales.Customers AS c
LEFT JOIN Sales.Orders AS o
    ON c.CustomerID = o.CustomerID
WHERE o.CustomerID IS NULL;

7. NULL vs Empty String vs Blank Spaces

7.1 Compare NULL / ‘’ / ’ ’

Task 8 – Demonstrate NULL vs empty vs blank Create a small dataset Orders with 4 rows:

1. Id = 1, Category = 'A'
2. Id = 2, Category = NULL
3. Id = 3, Category = '' (empty string)
4. Id = 4, Category = ' ' (two spaces)

Then select:

• All columns (*)
• LenCategory = DATALENGTH(Category)
• Policy1 = TRIM(Category)
• Policy2 = NULLIF(TRIM(Category), '')
• Policy3 = COALESCE(NULLIF(TRIM(Category), ''), 'unknown')

to see how NULL, empty string, and blank spaces behave differently.

WITH Orders AS (
    SELECT 1 AS Id, 'A' AS Category UNION
    SELECT 2, NULL UNION
    SELECT 3, '' UNION
    SELECT 4, '  '
)
SELECT 
    *,
    DATALENGTH(Category) AS LenCategory,
    TRIM(Category) AS Policy1,
    NULLIF(TRIM(Category), '') AS Policy2,
    COALESCE(NULLIF(TRIM(Category), ''), 'unknown') AS Policy3
FROM Orders;